set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

with t1 as (
    select --签收扫描
          waybill_no  as waybill_no
         ,signed_time as scan_time
         ,signed_site_code as scan_site_code
         ,signed_site as scan_site
         ,null as partner_name
         ,1 as type
         ,'签收' as type_name
         ,if(signed_site_code is not null,1,0) as tag
         ,if(source = '共配',1,0) as if_commen --是否共配
         ,null as last_partner_name --最晚扫描机构
         ,first_value(signed_site_code) over(partition by waybill_no order by signed_time desc) as last_scan_site_code --最晚扫描网点code
         ,first_value(signed_site)      over(partition by waybill_no order by signed_time desc) as last_scan_site --最晚扫描网点名称
         ,first_value(deliver_user)     over(partition by waybill_no order by signed_time desc) as last_deliver_user --最晚派件员
         ,first_value(pistol_id)        over(partition by waybill_no order by signed_time desc) as pistol_id --最晚品牌名称
    from jms_dwd.dwd_tab_barscan_sign_base_dt ts
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and to_date(signed_time) between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and source <> '菜鸟' and source <> '邮政'--20230425剔除签收来源为菜鸟或邮政的单子
    union all
    select --出库扫描
          waybill_no
         ,scan_time
         ,scan_site_code
         ,scan_site
         ,partner_name
         ,2 as type
         ,'出库' as type_name
         ,if(scan_site_code is not null,1,0) as tag
         ,if(source = '共配',1,0) as if_commen --是否共配
         ,first_value(partner_name)   over(partition by waybill_no order by scan_time desc) as last_partner_name --最晚扫描机构
         ,first_value(scan_site_code) over(partition by waybill_no order by scan_time desc) as last_scan_site_code --最晚扫描网点code
         ,first_value(scan_site)      over(partition by waybill_no order by scan_time desc) as last_scan_site --最晚扫描网点名称
         ,null as last_deliver_user --最晚派件员
         ,first_value(pistol_id)      over(partition by waybill_no order by scan_time desc) as pistol_id --最晚品牌名称
    from jms_dwd.dwd_tab_barscan_collect_base_dt tc
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and scan_type = '出库扫描'
    union all
    select --入库扫描
          waybill_no
         ,scan_time
         ,scan_site_code
         ,scan_site
         ,partner_name
         ,3 as type
         ,'入库' as type_name
         ,if(scan_site_code is not null,1,0) as tag
         ,null as if_commen --是否共配
         ,first_value(partner_name)   over(partition by waybill_no order by scan_time desc) as last_partner_name --最晚扫描机构
         ,first_value(scan_site_code) over(partition by waybill_no order by scan_time desc) as last_scan_site_code --最晚扫描网点code
         ,first_value(scan_site)      over(partition by waybill_no order by scan_time desc) as last_scan_site --最晚扫描网点名称
         ,null as last_deliver_user --最晚派件员
         ,null as pistol_id
    from jms_dwd.dwd_tab_barscan_collect_base_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and scan_type = '入库扫描'
    union all
    select --派件扫描
          waybill_no
         ,scan_time
         ,scan_site_code
         ,scan_site
         ,null as partner_name
         ,4 as type
         ,'出仓' as type_name
         ,if(scan_site_code is not null,1,0) as tag
         ,null as if_commen --是否共配
         ,null as last_partner_name --最晚扫描机构
         ,first_value(scan_site_code) over(partition by waybill_no order by scan_time desc) as last_scan_site_code --最晚扫描网点code
         ,first_value(scan_site)      over(partition by waybill_no order by scan_time desc) as last_scan_site --最晚扫描网点名称
         ,null as last_deliver_user --最晚派件员
         ,null as pistol_id
    from jms_dwd.dwd_tab_barscan_deliver_base_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
    union all
    select --卸车扫描
          waybill_no
         ,scan_time
         ,scan_site_code
         ,scan_site
         ,null as partner_name
         ,5 as type
         ,'卸车' as type_name
         ,if(scan_site_code is not null,1,0) as tag
         ,null as if_commen --是否共配
         ,null as last_partner_name --最晚扫描机构
         ,first_value(scan_site_code) over(partition by waybill_no order by scan_time desc) as last_scan_site_code --最晚扫描网点code
         ,first_value(scan_site)      over(partition by waybill_no order by scan_time desc) as last_scan_site --最晚扫描网点名称
         ,null as last_deliver_user --最晚派件员
         ,null as pistol_id
    from jms_dwd.dwd_tab_barscan_unloading_base_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and scan_type = '卸车扫描'
      and (waybill_no like "JT%" or waybill_no like "55%")
    union all
    select  --取出扫描
        waybill_no
         ,scan_time
         ,scan_site_code
         ,scan_site
         ,null as partner_name
         ,6 as type
         ,'取出' as type_name
         ,if(scan_site_code is not null,1,0) as tag
         ,null as if_commen --是否共配
         ,null as last_partner_name --最晚扫描机构
         ,first_value(scan_site_code) over(partition by waybill_no order by scan_time desc) as last_scan_site_code --最晚扫描网点code
         ,first_value(scan_site)      over(partition by waybill_no order by scan_time desc) as last_scan_site --最晚扫描网点名称
         ,null as last_deliver_user --最晚派件员
         ,null as pistol_id
    from jms_dwd.dwd_tab_barscan_collect_base_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',15) and  '{{ execution_date | cst_ds }}'
      and scan_type = '快件取出扫描'
),
t2 as (  --签收/出库取最早，其他取最晚，签收出库后还有操作数据,为异常
    select
          *
        ,if(nvl(sign_time,'2099-12-31 00:00:00') < nvl(outstore_time,'2099-12-31 00:00:00'),sign_time,outstore_time) as sign_first_time --最早签收/出库时间
        ,if(nvl(sign_time,'2099-12-31 00:00:00') < nvl(outstore_time,'2099-12-31 00:00:00'),sign_pistol_id,outstore_pistol_id) as sign_first_pistol_id --最早签收/出库品牌名称
    from (
         select
              waybill_no
             ,min(if(type=1,scan_time,null)) as sign_time            --最早签收时间
             ,min(if(type=2,scan_time,null)) as outstore_time        --最早出库时间
             ,min(if(type=6,scan_time,null)) as getout_time          --最晚取出时间
             ,max(if(type=1,scan_time,null)) as last_sign_time       --最晚签收时间
             ,max(if(type=2,scan_time,null)) as last_outstore_time   --最晚出库时间
             ,max(if(type=3,scan_time,null)) as last_instore_time    --最晚入库时间
             ,max(if(type=4,scan_time,null)) as last_deliver_time    --最晚出仓时间
             ,max(if(type=5,scan_time,null)) as last_unload_time     --最晚卸车时间
             ,max(if(type=1,last_scan_site_code,null)) as last_sign_scan_site_code       --最晚签收网点code
             ,max(if(type=2,last_scan_site_code,null)) as last_outstore_scan_site_code   --最晚出库网点code
             ,max(if(type=2,last_scan_site_code,null)) as last_instore_scan_site_code    --最晚入库网点code 20230906新增
             ,max(if(type=4,last_scan_site_code,null)) as last_deliver_scan_site_code    --最晚出仓网点code
             ,max(if(type=5,last_scan_site_code,null)) as last_unload_scan_site_code     --最晚卸车网点code
             ,max(if(type=1,last_scan_site,null)) as last_sign_scan_site      --最晚签收网点名称
             ,max(if(type=2,last_scan_site,null)) as last_outstore_scan_site  --最晚出库网点名称
             ,max(if(type=3,last_scan_site,null)) as last_instore_scan_site   --最晚入库网点名称20230906新增
             ,max(if(type=4,last_scan_site,null)) as last_deliver_scan_site   --最晚出仓网点名称
             ,max(if(type=5,last_scan_site,null)) as last_unload_scan_site    --最晚卸车网点名称
             ,max(if(type=2,last_partner_name,null)) as last_outstore_partner_name   --最晚出库机构
             ,max(if(type=3,last_partner_name,null)) as last_instore_partner_name    --最晚入库机构
             ,max(if_commen) as if_commen --是否共配
             ,max(if(type=1,last_deliver_user,null)) as last_sign_deliver_user --最晚签收派件员
             ,max(if(type=1,pistol_id,null)) as sign_pistol_id     --最晚签收品牌名称
             ,max(if(type=2,pistol_id,null)) as outstore_pistol_id --最晚出库品牌名称
         from t1
         group by waybill_no
    ) a
),
t3 as ( --取签收网点，签收->出库->入库->出仓->卸车,正常数据
    select waybill_no,type_name,scan_site_code,scan_site
    from (
        select
              t1.waybill_no
             ,t1.type_name
             ,t1.scan_site_code
             ,t1.scan_site
             ,row_number() over(partition by t1.waybill_no order by t1.type asc,t1.scan_time desc) as rn
        from t1
        left join t2 on t1.waybill_no = t2.waybill_no
        where t1.scan_time <= t2.sign_first_time
          and t1.type <> 6
          and t1.tag = 1
    ) a where a.rn = 1
)


insert overwrite table jms_dm.dm_waybill_sign_abnormal_detail_dt
select
      waybill_no                --签收/出库单
     ,sign_first_time           --签收/出库时间
     ,sign_first_code           --签收/出库网点code
     ,sign_first_name           --签收/出库网点名称
     ,type                      --类型(签收/出库)
     ,order_source_code         --订单来源code
     ,order_source_name         --订单来源名称
     ,sys_regional_code         --管理大区名称
     ,sys_regional_name         --管理大区code
     ,franchisee_code           --签收/出库加盟商code
     ,franchisee_name           --签收/出库加盟商名称
     ,agent_code                --签收/出库代理区code
     ,agent_name                --签收/出库代理区code
     ,max(if_l3d_deliver)       --是否出前三天出仓
     ,unload_time               --卸车到件时间
     ,unload_network_code       --卸车到件网点编码
     ,unload_network_name       --卸车到件网点名称
     ,deliver_time              --派件(出仓)扫描时间
     ,deliver_network_code      --派件(出仓)网点code
     ,deliver_network_name      --派件(出仓)网点名称
     ,instore_time              --入库时间
     ,instore_org_name          --入库机构
     ,outstore_time             --出库时间
     ,outstore_org_name         --出库机构
     ,sign_time                 --签收时间
     ,sign_network_code         --签收网点code
     ,sign_network_name         --签收网点名称
     ,getout_time               --快件取出时间
     ,if_return                 --是否退转件
     ,if_commen                 --是否共配
     ,deliver_send_user         --出仓派件员
     ,case when ((if_return = '否' and (type = '签收' or type = '出库')
                  and nvl(unload_time,'0000-00-00 00:00:00') <= sign_first_time
                  and nvl(deliver_time,'0000-00-00 00:00:00') <= sign_first_time
                  and nvl(getout_time,'0000-00-00 00:00:00') <= sign_first_time)
           or (if_return = '是' and type = '签收' and nvl(instore_time,'0000-00-00 00:00:00') > sign_first_time)
           or (type = '卸车' and nvl(sign_time,'0000-00-00 00:00:00') > sign_first_time))
           and if_tichu = 0 -- 是否转邮网点
           and if_sign_del <> 1 --剔除签收删除
           then 1 else 0 end as if_fine
     ,if_mail_network --是否转邮网点
     ,business_name     --品牌名称
     ,if_sign_in_equal  --签收入库是否同一网点
     ,if_sign_out_equal --签收出库是否同一网点
     ,if_sign_del       --是否签收删除
     ,dt
from(
    select
          sign.waybill_no                   as   waybill_no           --签收/出库运单
         ,sign.sign_first_time              as   sign_first_time      --签收/出库时间
         ,dim.code                          as   sign_first_code      --签收/出库网点code
         ,dim.name                          as   sign_first_name      --签收/出库网点名称
         ,t3.type_name                      as   type                 --类型（签收->出库->入库->出仓->卸车）
         ,orders.order_source_code          as   order_source_code    --订单来源code
         ,orders.order_source_name          as   order_source_name    --订单来源名称
         ,dim.sys_regional_name             as   sys_regional_name    --管理大区名称
         ,dim.sys_regional_code             as   sys_regional_code    --管理大区code
         ,dim.parent_network_code           as   franchisee_code      --签收/出库加盟商code
         ,dim.parent_network_name           as   franchisee_name      --签收/出库加盟商名称
         ,dim.financial_center_code         as   agent_code           --签收/出库代理区code
         ,dim.financial_center_desc         as   agent_name           --签收/出库代理区名称
         ,if(der.scan_time < sign.sign_first_time and datediff(sign.sign_first_time,der.scan_time)<=3,1,0) as if_l3d_deliver --是否出前三天出仓
         ,nvl(sign.last_sign_deliver_user,der.deliver_send_user)  as deliver_send_user  --签收/出仓派件员
         ,sign.last_unload_time             as   unload_time          --最晚卸车时间
         ,sign.last_unload_scan_site_code   as   unload_network_code  --最晚卸车网点code
         ,sign.last_unload_scan_site        as   unload_network_name  --最晚卸车网点名称
         ,sign.last_deliver_time            as   deliver_time         --最晚出仓时间
         ,sign.last_deliver_scan_site_code  as   deliver_network_code --最晚出仓网点code
         ,sign.last_deliver_scan_site       as   deliver_network_name --最晚出仓网点名称
         ,sign.last_instore_time            as   instore_time         --最晚入库时间
         ,sign.last_instore_partner_name    as   instore_org_name     --最晚入库机构
         ,sign.last_outstore_time           as   outstore_time        --最晚出库时间
         ,sign.last_outstore_partner_name   as   outstore_org_name    --最晚出库机构
         ,sign.last_sign_time               as   sign_time            --最晚签收时间
         ,sign.last_sign_scan_site_code     as   sign_network_code    --最晚签收网点code
         ,sign.last_sign_scan_site          as   sign_network_name    --最晚签收网点名称
         ,sign.getout_time                  as   getout_time          --最早取出时间
         ,case when express.waybill_no is not null then '是' else '否' end as if_return --是否退转件
         ,case when sign.if_commen = 1 then '是' else '否' end as if_commen --是否共配
         ,case when config.network_code is not null then 1 else 0 end as if_mail_network --是否转邮网点
         ,ter.business_name as business_name
         ,case when sign.last_sign_scan_site_code = sign.last_instore_scan_site  then 1 else 0 end as if_sign_in_equal  --签收入库是否同一网点
         ,case when sign.last_sign_scan_site_code = sign.last_outstore_scan_site then 1 else 0 end as if_sign_out_equal --签收出库是否同一网点
         ,case when sign_del.waybill_no is not null then 1 else 0 end as if_sign_del --是否签收删除
         ,case when  config.network_code is not null and (sign.last_instore_partner_name = '中邮E通' or sign.last_outstore_partner_name = '中邮E通') then 1 else 0 end as if_tichu
         ,to_date(sign.sign_first_time) as dt
    from t2 as sign
    left join t3
       on sign.waybill_no = t3.waybill_no
    left join(
        --订单表 取订单来源
        select
             waybill_no
            ,ordersource_code as order_source_code
            ,ordersource_name as order_source_name
        from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',21) and  '{{ execution_date | cst_ds }}'
    ) orders on orders.waybill_no = sign.waybill_no
    left join (
        select
             id
             ,code
             ,name
             ,parent_network_code --加盟商code
             ,parent_network_name --加盟商名称
             ,financial_center_id --代理区id
             ,financial_center_code --代理区code
             ,financial_center_desc --代理区名称
             ,regional_id --大区id
             ,regional_desc --大区名称
             ,manage_regional_code as sys_regional_code --管理大区code
             ,manage_regional_name as sys_regional_name --管理大区名称
             ,is_entrepot --是否集散
             ,type_id
        from jms_dim.dim_lmdm_sys_network_manage
        where is_entrepot = 2
          and type_id = 336
    ) dim on t3.scan_site_code = dim.code
    left join ( --判断是否退转件
        select *
        from (
             select waybill_no,row_number() over(partition by waybill_no order by examine_time desc ) rn
             from jms_dwd.dwd_tab_reback_transfer_express_base   --ods层全量抽
             where status=2
        ) a where a.rn = 1
    )express on sign.waybill_no = express.waybill_no
    left join (  --判断是否签收前三天出仓
        select
              waybill_no
             ,scan_time
             ,first_value(send_user) over (partition by waybill_no order by scan_time desc) as deliver_send_user
        from jms_dwd.dwd_tab_barscan_deliver_base_dt td
        where dt between date_sub('{{ execution_date | cst_ds }}',18) and  '{{ execution_date | cst_ds }}'
          and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',18) and '{{ execution_date | cst_ds }}'
    ) der on sign.waybill_no = der.waybill_no
    left join (
        select *
        from jms_dim.dim_tab_abnormal_network_config_base_dt
        where dt = '{{ execution_date | cst_ds }}'
        and is_delete = '1'
        and application_menu = '轨迹异常签收'
    ) config on config.network_code = dim.code
     left join (
        select *
        from (
            select
                 network_code
                ,end_brand_code
                ,end_brand_name as business_name
                ,four_in_one
                ,row_number() over(partition by network_code order by end_brand_name !='溪鸟',create_time desc ) rn
            from jms_dim.dim_jds_comatch_configuration_base
            where valid='1'
            and config_type='1'
        ) a where a.rn=1
    ) ter on ter.network_code = dim.code
    left join (
        select billcode as waybill_no
        from jms_dwd.dwd_tab_barscan_sign_delete_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',15) and'{{ execution_date | cst_ds }}'
        group by billcode
    ) sign_del on sign_del.waybill_no = sign.waybill_no
    where (sign.waybill_no like "JT%" or sign.waybill_no like "55%")
      and (sign.last_unload_time    > sign.sign_first_time or
           sign.last_deliver_time   > sign.sign_first_time or
           sign.last_instore_time   > sign.sign_first_time or
           sign.last_outstore_time  > sign.sign_first_time or
           sign.last_sign_time      > sign.sign_first_time)
      and to_date(sign.sign_first_time) = '{{ execution_date | cst_ds }}'
      and !(t3.type_name = '出库' and sign.last_sign_time is null and sign.last_sign_scan_site_code is null)
) a group by waybill_no
            ,sign_first_time
            ,sign_first_code
            ,sign_first_name
            ,type
            ,order_source_code
            ,order_source_name
            ,sys_regional_code
            ,sys_regional_name
            ,franchisee_code
            ,franchisee_name
            ,agent_code
            ,agent_name
            ,unload_time
            ,unload_network_code
            ,unload_network_name
            ,deliver_time
            ,deliver_network_code
            ,deliver_network_name
            ,instore_time
            ,instore_org_name
            ,outstore_time
            ,outstore_org_name
            ,sign_time
            ,sign_network_code
            ,sign_network_name
            ,getout_time
            ,if_return
            ,if_commen
            ,deliver_send_user
            ,case when ((if_return = '否' and (type = '签收' or type = '出库')
                         and nvl(unload_time,'0000-00-00 00:00:00') <= sign_first_time
                         and nvl(deliver_time,'0000-00-00 00:00:00') <= sign_first_time
                         and nvl(getout_time,'0000-00-00 00:00:00') <= sign_first_time)
                  or (if_return = '是' and type = '签收' and nvl(instore_time,'0000-00-00 00:00:00') > sign_first_time)
                  or (type = '卸车' and nvl(sign_time,'0000-00-00 00:00:00') > sign_first_time))
                  and if_tichu = 0
                  and if_sign_del <> 1 --剔除签收删除
                  then 1 else 0 end
            ,if_mail_network --是否转邮网点
            ,business_name     --品牌名称
            ,if_sign_in_equal  --签收入库是否同一网点
            ,if_sign_out_equal --签收出库是否同一网点
            ,if_sign_del       --是否签收删除
            ,dt
distribute by dt,pmod(hash(rand()),5);
